package demo.dao;

import demo.entity.employees;
import demo.entity.managers;
import demo.entity.record;
import demo.mappers.empRowMapper;
import demo.mappers.managersRowMapper;
import demo.mappers.recordRowMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

@Service
public class HigherDao {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    public  List<employees> queryAllEmployees(String depart){
        String sql = "SELECT * FROM employees WHERE department_id=? ";
        List<employees> emp = jdbcTemplate.query(sql, new empRowMapper(), depart);
        return emp;
    }

    public employees queryEmployeesById(int id) {
        String sql = "SELECT * FROM employees WHERE emp_id=? ";
        employees emp = jdbcTemplate.queryForObject(sql, new empRowMapper(), id);
        return emp;
    }
    public employees changeBasic_Salary(int emp_id, int newBasic_Salary){
        String sql = "UPDATE employees SET basic_Salary=? WHERE emp_id=? ";
        jdbcTemplate.update(sql, newBasic_Salary,emp_id);
        return queryEmployeesById(emp_id);
    }
    public  employees changeMonthly_Salary(int emp_id, int newMonthly_Salary){
        String sql = "UPDATE employees SET monthly_Salary=? WHERE emp_id=? ";
        jdbcTemplate.update(sql, newMonthly_Salary,emp_id);
        return queryEmployeesById(emp_id);
    }
    public employees changeArrival_time(int emp_id, String Arrival_time){
        String sql = "UPDATE employees SET arrival_time=? WHERE emp_id=? ";
        jdbcTemplate.update(sql, Arrival_time,emp_id);
        return queryEmployeesById(emp_id);
    }
    public employees changeDeparture_time(int emp_id, String departure_time){
        String sql = "UPDATE employees SET departure_time=? WHERE emp_id=? ";
        jdbcTemplate.update(sql, departure_time,emp_id);
        return queryEmployeesById(emp_id);
    }
    public List<record> queryAllRecord(String depart) {
        String sql = "SELECT * FROM record WHERE department_id = ? ORDER BY id";
        return jdbcTemplate.query(sql, new recordRowMapper(), depart);
    }
    public managers addManagers(managers man) {
        Date date = new Date();
        SimpleDateFormat dateTimeFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String dateTime = dateTimeFormat.format(date);
        String sql = "INSERT INTO managers  VALUES(?,?,?,?,?,?,?,?,?,?,?)";
        String weichuli=" 未处理 ";
        jdbcTemplate.update(sql, null, man.getEmp_id(),man.getName(),man.getDepartment_id(),man.getJob_id(),man.getReason(),weichuli,man.getStart_date(),man.getEnd_date(),dateTime,null);
        String sql2 = "SELECT * FROM managers WHERE apply_time = ? ";
        return jdbcTemplate.queryForObject(sql2, new managersRowMapper(),dateTime);
    }
    public List<managers> queryManagers(String department){ //获取本部门请假记录
        String sql = "SELECT * FROM managers WHERE department_id=? ";
        List<managers> manList = jdbcTemplate.query(sql, new managersRowMapper(),department);
        return manList;
    }
    public managers queryManagersById(int id, String department){ //获取本部门某id的请假记录
        String sql = "SELECT * FROM managers WHERE id = ? AND department_id = ?";
        managers man = jdbcTemplate.queryForObject(sql, new managersRowMapper(),id,department);
        return man;
    }
    public managers queryAllManagersById(int id){ //获取全体请假记录
        String sql = "SELECT * FROM managers WHERE id = ? ";
        managers man = jdbcTemplate.queryForObject(sql, new managersRowMapper(),id);
        return man;
    }
    public managers dealManagers(int id, String status){
        Date date = new Date();
        SimpleDateFormat dateTimeFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String dateTime = dateTimeFormat.format(date);
        String sql = "UPDATE managers SET status=?,deal_time=? WHERE id=? ";
        jdbcTemplate.update(sql, status,dateTime,id);
        return queryAllManagersById(id);
    }
    public employees changeRestHoliday(int emp_id, int newRestHoliday){
        String sql = "UPDATE employees SET rest_holiday=? WHERE emp_id=? ";
        jdbcTemplate.update(sql, newRestHoliday,emp_id);
        return queryEmployeesById(emp_id);
    }
    public employees worning(){
        employees worn = new employees();
        worn.setName("权限不足");
        return worn;
    }
    public managers managers_Worning(){
        managers worn = new managers();
        worn.setReason("权限不足");
        worn.setStatus("处理失败");
        return worn;
    }
    public managers no_Moreworning() {
        managers worn = new managers();
        worn.setReason("剩余假期不足！");
        worn.setStatus("申请失败");
        return worn;
    }
}
